/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package smartgrid;

import ETL.ETL;
import ETL.dayLine;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import org.hibernate.Hibernate;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.orm.PersistentException;
import smartgrid.exceptions.NoDataAvailableException;

/**
 *
 * @author daniel
 */
@Stateless
public class EltricStatsManager implements EltricStatsManagerLocal {

    public static final short NONE = 0;
    public static final short DATELINE = 1;
    public static final short DAYS = 2;
    public static final short WEEKS = 3;
    public static final short MONTHS = 4;
    public static final int SHOW_DAYS = 7;
    public static final int SHOW_MONTHS = 90;

    @Override
    public EltricStats getElectricStatsByMaterializedDay(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        EltricStats ele = new EltricStats();
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(DAYS);

        TreeSet<DataLine> linha = new TreeSet<DataLine>();

        DiaAgregadoCriteria dCrit;
        try {
            dCrit = new DiaAgregadoCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));

            DiaAgregado[] dias = DiaAgregado.listDiaAgregadoByCriteria(dCrit);
            DiaAgregado d;
            DataLine dltemp;
            System.out.println("CL dia " + dias.length);
            Iterator<DiaAgregado> i = (Arrays.asList(dias)).iterator();
            while (i.hasNext()) {
                d = i.next();
                dltemp = new DataLine();
                dltemp.setContract(d.getContract());
                dltemp.setTariff(d.getContract().getTariff_CurrentTarif_role());
                dltemp.setHora((short) 0);
                dltemp.setMinuto((short) 0);
                dltemp.setUnit("kW");
                dltemp.setTimeStamp(d.getData());
                dltemp.setValue(d.getGastoSUM());
                linha.add(dltemp);
            }

        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        ele.setDataLine_role(linha);
        return ele;
    }

    public EltricStats getElectricStatsByMaterializedMonth(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        EltricStats ele = new EltricStats();
        begin.set(Calendar.DAY_OF_MONTH, 1);
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(MONTHS);
        TreeSet<DataLine> linha = new TreeSet<DataLine>();

        MesAgregadoCriteria dCrit;
        try {
            dCrit = new MesAgregadoCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));

            MesAgregado[] meses = MesAgregado.listMesAgregadoByCriteria(dCrit);
            MesAgregado d;
            DataLine dltemp;
            if (meses == null || Arrays.asList(meses).isEmpty()) {
                return null;
            }
            Iterator<MesAgregado> i = (Arrays.asList(meses)).iterator();
            while (i.hasNext()) {
                d = i.next();
                dltemp = new DataLine();
                dltemp.setContract(d.getContract());
                dltemp.setTariff(d.getContract().getTariff_CurrentTarif_role());
                dltemp.setHora((short) 0);
                dltemp.setMinuto((short) 0);
                dltemp.setUnit("kW");
                dltemp.setTimeStamp(d.getData());
                dltemp.setValue(d.getGastoSUM());
                linha.add(dltemp);
            }
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        ele.setDataLine_role(linha);
        return ele;
    }

    private static DataLine criaDataline(Contract contrato, Tariff tarrif, Date data, Double value) {
        DataLine dltemp = new DataLine();
        dltemp.setContract(contrato);
        dltemp.setUnit("kW");
        dltemp.setHora((short) 0);
        dltemp.setMinuto((short) 0);
        dltemp.setTimeStamp(data);
        dltemp.setTariff(tarrif);
        dltemp.setValue(value);
        return dltemp;
    }

    public EltricStats getElectricStatsByMaterializedWeeks(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        EltricStats ele = new EltricStats();
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(WEEKS);
        TreeSet<DataLine> linha = new TreeSet<DataLine>();

        SemanaAgregadaCriteria dCrit;
        try {
            dCrit = new SemanaAgregadaCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));
//            dCrit.setProjection(Projections.projectionList().add(Projections.sum("domingoPrecoSUM")).add(Projections.sum("segundaPrecoSUM")).add(Projections.sum("tercaPrecoSUM")).add(Projections.sum("quartaPrecoSUM")).add(Projections.sum("quintaPrecoSUM")).add(Projections.sum("sextaPrecoSUM")).add(Projections.sum("sabadoPrecoSUM")).add(Projections.avg("domingoPrecoAVG")).add(Projections.avg("segundaPrecoAVG")).add(Projections.avg("tercaPrecoAVG")).add(Projections.avg("quartaPrecoAVG")).add(Projections.avg("quintaPrecoAVG")).add(Projections.avg("sextaPrecoAVG")).add(Projections.avg("sabadoPrecoAVG")).add(Projections.sum("domingoGastoSUM")).add(Projections.sum("segundaGastoSUM")).add(Projections.sum("tercaGastoSUM")).add(Projections.sum("quartaGastoSUM")).add(Projections.sum("quintaGastoSUM")).add(Projections.sum("sextaGastoSUM")).add(Projections.sum("sabadoGastoSUM")).add(Projections.avg("domingoGastoAVG")).add(Projections.avg("segundaGastoAVG")).add(Projections.avg("tercaGastoAVG")).add(Projections.avg("quartaGastoAVG")).add(Projections.avg("quintaGastoAVG")).add(Projections.avg("sextaGastoAVG")).add(Projections.avg("sabadoGastoAVG")));
            dCrit.setProjection(Projections.projectionList().add(Projections.sum("domingoGastoSUM")).add(Projections.sum("segundaGastoSUM")).add(Projections.sum("tercaGastoSUM")).add(Projections.sum("quartaGastoSUM")).add(Projections.sum("quintaGastoSUM")).add(Projections.sum("sextaGastoSUM")).add(Projections.sum("sabadoGastoSUM")).add(Projections.avg("domingoGastoAVG")));
            SemanaAgregada d;

            /* materializar variaveis*/
            Contract contrato = Contract.getContractByORMID(c.getID());
            DataLine dltemp;

            List<Object[]> rows = dCrit.list();

//            SemanaAgregada[] semanas = SemanaAgregada.listSemanaAgregadaByCriteria(dCrit);
            if (rows != null && rows.get(0)[0] != null) {
                if (!(rows.isEmpty())) {
                    Object[] row = rows.get(0);
//                Date data = ETL.gCalenderToSqlDate(begin);
                    Tariff tarrif = null;

                    /* DOMINGO */
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[0]);
                    linha.add(dltemp);

                    /* SEGUNDA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = new DataLine();
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[1]);
                    linha.add(dltemp);

                    /* TERCA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[2]);
                    linha.add(dltemp);

                    /* QUARTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[3]);
                    linha.add(dltemp);

                    /* QUINTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[4]);
                    linha.add(dltemp);

                    /* SEXTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[5]);
                    linha.add(dltemp);

                    /* SABADO */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[6]);
                    linha.add(dltemp);
                }
            }
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        ele.setDataLine_role(linha);
        return ele;
    }

    @Override
    public EltricStats getEletricStats(GregorianCalendar begin, GregorianCalendar end, Contract contract, short type) throws NoDataAvailableException {

        EltricStats stats = new EltricStats();
        GregorianCalendar startDate = new GregorianCalendar();
        GregorianCalendar endDate = new GregorianCalendar();

        //int diff = 30;
        startDate = (GregorianCalendar) begin.clone();
        endDate = (GregorianCalendar) end.clone();
        Date s = startDate.getTime();
        Date e = endDate.getTime();
        long dateDiff = e.getTime() - s.getTime();

        if (type == NONE || (type == WEEKS && (dateDiff / (1000 * 60 * 60 * 24)) < 7)
                || (type == DATELINE && (dateDiff / (1000 * 60 * 60 * 24)) >= SHOW_DAYS)) {
            if (dateDiff / (1000 * 60 * 60 * 24) <= SHOW_DAYS) {
                System.out.println("dataline");
                stats = (this.getStatsHour(begin, end, contract));
            } else if (dateDiff / (1000 * 60 * 60 * 24) > SHOW_DAYS && dateDiff / (1000 * 60 * 60 * 24) <= SHOW_MONTHS) {
                System.out.println("DIA");
                stats = this.getElectricStatsByMaterializedDay(begin, end, contract);
            } else {
                System.out.println("MES");
                stats = this.getElectricStatsByMaterializedMonth(begin, end, contract);
            }

        } else {
            switch (type) {
                case DATELINE:
                    stats = this.getStatsHour(begin, end, contract);
                    System.out.println("switch dataline");
                    break;
                case DAYS:
                    System.out.println("SWITCH DIA");
                    stats = this.getElectricStatsByMaterializedDay(begin, end, contract);
                    break;
                case WEEKS:
                    System.out.println("SWITCH Week");
                    stats = this.getElectricStatsByMaterializedWeeks(begin, end, contract);
                    break;
                case MONTHS:
                    System.out.println("SWITCH MOUNTH");
                    stats = this.getElectricStatsByMaterializedMonth(begin, end, contract);
                    if (stats == null) {
                        if (end.get(Calendar.MONTH) == new GregorianCalendar().get(Calendar.MONTH) && end.get(Calendar.YEAR) == new GregorianCalendar().get(Calendar.YEAR)) {
                            if ((begin.get(Calendar.MONTH) - 1) == new GregorianCalendar().get(Calendar.MONTH)) {
                                begin.set(Calendar.DAY_OF_MONTH, 1);
                                stats = this.getElectricStatsByMaterializedMonth(begin, end, contract);
                            } else {
                                throw new NoDataAvailableException();
                            }
                        } else {
                            throw new NoDataAvailableException();
                        }
                    }

                    break;
                default:
                    stats = null;
                    break;
            }
        }

        return stats;


    }

    private EltricStats getStatsHour(GregorianCalendar begin, GregorianCalendar end, Contract contract) {
        EltricStats stats = new EltricStats();
        // String data_inicio = begin.get(Calendar.YEAR) + "-" + (begin.get(Calendar.MONTH) + 1) + "-" + begin.get(Calendar.DAY_OF_MONTH);
        //String data_fim = end.get(Calendar.YEAR) + "-" + (end.get(Calendar.MONTH) + 1) + "-" + end.get(Calendar.DAY_OF_MONTH);

        DataLineCriteria dtLineCrit;
        try {
            dtLineCrit = new DataLineCriteria();
            dtLineCrit.add(Restrictions.between("timeStamp", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dtLineCrit.add(Restrictions.eq("contract.id", contract.getORMID()));
            DataLine[] horas = DataLine.listDataLineByCriteria(dtLineCrit);
            stats.setDataLine_role(Arrays.asList(horas));
            stats.setInterval(DATELINE); //TODO
            stats.setStartTime(begin);
            stats.setEndTime(end);

            return stats;
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public static List<dayLine> getDayLines(GregorianCalendar day, org.hibernate.Session ss) {
        // try {
        Date d;
        int ano;
        int mes;
        int dia;
        int hora = day.get(Calendar.HOUR_OF_DAY);
        int minuto = day.get(Calendar.MINUTE);
        String data = (day.get(Calendar.YEAR)) + "-" + (day.get(Calendar.MONTH) + 1) + "-" + day.get(Calendar.DAY_OF_MONTH);
        //ss= s.getCurrentSession();
        System.out.println("HOJE: " + data + " :" + hora + ":" + minuto);
        List<Object[]> datalines = ss.createSQLQuery("SELECT  contractid, "
                + "SUM(dl.value) as \"gastosum\", "
                + "AVG(dl.\"value\") as \"gastoavg\","
                + "SUM(p.euros * dl.\"value\") as \"precosum\","
                + "AVG(p.euros*dl.\"value\") as \"precoavg\", "
                + "(select unit from dataline dl1 where dl1.tariffid = tariffid limit 1) as Unidade, "
                + "\"timestamp\" as data, "
                + "MAX(hora) as hora, "
                + "MAX(minuto) as minuto "
                + "FROM dataline dl, price p "
                + "where ((dl.hora = p.starthour and dl.minuto >= p.startminute) "
                + "or    (dl.hora > p.starthour)) "
                + "and   ((dl.hora = p.endhour and dl.minuto <= p.endminute) "
                + "or    (dl.hora < p.endhour)) and dl.tariffid = p.tariffid and \"timestamp\" = '" + data + "' "
                + "group by contractid, \"timestamp\" "
                + "order by contractid, \"timestamp\" ").addScalar("contractid", Hibernate.INTEGER).
                addScalar("gastosum", Hibernate.FLOAT).addScalar("gastoavg", Hibernate.FLOAT).addScalar("precosum", Hibernate.FLOAT).addScalar("precoavg", Hibernate.FLOAT).addScalar("unidade", Hibernate.STRING).addScalar("data", Hibernate.DATE).list();
        System.out.println("Tamanho: " + datalines.size());
        dayLine b;
        Object[] bb;
        List<dayLine> dayLines = new ArrayList<dayLine>();
        // for(MesAgregado mes : datalines)
        for (Object[] a : datalines) {
            bb = a;

            dayLines.add(new dayLine((Integer) bb[0], (String) bb[5], ETL.postgresDateToGCalendar((Date) bb[6]), (Float) bb[2], (Float) bb[1], (Float) bb[4], (Float) bb[3]));

        }
        for (dayLine a : dayLines) {
            System.out.print("-----------------------------------------\n");
            System.out.println("\nnumero de elementos " + a.getContractID());
            System.out.println("\nnumero de elementos " + a.getGastoAVG());
            System.out.println("\nnumero de elementos " + a.getGastoSUM());
            System.out.println("\nnumero de elementos " + a.getPrecoAVG());
            System.out.println("\nnumero de elementos " + a.getPrecoSUM());
            System.out.println("Data " + a.getData().get(Calendar.MONTH));
        }

        return dayLines;
    }

    @Override
    public TreeSet<RankingElem> getRanking(ArrayList<String> amigos) {
        TreeSet<RankingElem> lista = new TreeSet<RankingElem>();
        RankingElem elemento;
        String fb;
        if(amigos == null || amigos.isEmpty())
            System.out.println("getRanking: lsta de amigos null");
        org.hibernate.Session ss;
        SessionFactory s = new AnnotationConfiguration().configure().buildSessionFactory();
        ss = (org.hibernate.Session) s.openSession();

        Iterator<String> i = amigos.iterator();
        
        GregorianCalendar gcal = new GregorianCalendar();
        gcal.add(Calendar.MONTH, -1);
        gcal.set(Calendar.DAY_OF_MONTH, 1);

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT f.uid as uid, sum(ma.gastoavg) as gasto FROM facebookaccount_contract as fc, mesagregado as ma , facebookaccount as f Where f.uid in ('");
        if (i.hasNext()) {
            fb = i.next();
            sb.append(fb);
        }
        while (i != null && i.hasNext()) {
            fb = i.next();
            sb.append("', '");
            sb.append(fb);
        }
        sb.append("') and fc.facebookaccountid = f.id AND ma.data ='");
        sb.append(gcal.get(Calendar.YEAR));
        sb.append("-");
        sb.append(gcal.get(Calendar.MONTH) + 1);
        sb.append("-");
        sb.append(gcal.get(Calendar.DAY_OF_MONTH));
        sb.append("' and ma.contractid = fc.contractid group by uid order by gasto desc, uid desc limit 10");

        List<Object[]> rows = (List<Object[]>) ss.createSQLQuery(sb.toString()).addScalar("uid", Hibernate.STRING).addScalar("gasto", Hibernate.DOUBLE).list();

        Iterator<Object[]> row = rows.iterator();

        while (row != null && row.hasNext()) {
            Object[] kk = row.next();
            elemento = new RankingElem((String) kk[0], (Double) kk[1]);
            lista.add(elemento);
        }

        return lista;
    }

    @Override
    public MoneyStats getMoneyPrevisions(Tariff tariff, EltricStats elStats) {
        try {
            if (elStats.getInterval() == DATELINE) {
                return this.getMoneyPrevisionsHour(tariff, elStats);
            }

            MoneyStats mStats = new MoneyStats();
            DataLine newDl;
            double minuteStart = 0;
            double minuteEnd = 0;
            double mediaPreco = 0;
            double gasto = 0;
            List<DataLine> dlMoney = new ArrayList<DataLine>();
            PriceCriteria priceCrit;
            Price[] precos;
            priceCrit = new PriceCriteria();
            priceCrit.add(Restrictions.eq("Tariff_Prices_role.id", tariff.getID()));
            precos = Price.listPriceByCriteria(priceCrit);

            for (Price p : precos) {
                minuteStart = p.getStartMinute() == 0 ? 0 : p.getStartMinute() / 60;
                minuteEnd = p.getEndMinute() == 0 ? 0 : p.getEndMinute() / 60;

                mediaPreco += ((((p.getEndHour() + minuteEnd)
                        - (p.getStartHour() + minuteStart))) / 24) * p.getEuros();
            }
            for (DataLine dLine : elStats.getDataLine_role()) {
                //média ponderada do preço do tarifário
                gasto = mediaPreco * dLine.getValue(); //valor monetário gasto neste data line

                newDl = new DataLine();
                newDl.setContract(dLine.getContract());
                newDl.setHora(dLine.getHora());
                newDl.setMinuto(dLine.getMinuto());
                newDl.setTariff(tariff);
                newDl.setTimeStamp(dLine.getTimeStamp());
                newDl.setUnit(dLine.getUnit());
                newDl.setValue(gasto);
                dlMoney.add(newDl);



            }

            mStats.setDataLine_role(dlMoney);
            mStats.setStartTime(elStats.getStartTime());
            mStats.setEndTime(elStats.getEndTime());
            mStats.setInterval(elStats.getInterval());
            return mStats;


        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;


    }

    private MoneyStats getMoneyPrevisionsHour(Tariff tariff, EltricStats elStats) {
        try {
            List<DataLine> Dlines = new ArrayList<DataLine>();
            DataLine dl;
            MoneyStats mstats = new MoneyStats();
            Price price = null;

            float totalMoney = 0;
            int dataLineHour = 0;
            int dataLineMin = 0;

            GregorianCalendar timeStamp = new GregorianCalendar();

            for (DataLine dataline : elStats.getDataLine_role()) {
                price = null;

                dataLineMin = timeStamp.get(Calendar.MINUTE);
                dataLineHour = timeStamp.get(Calendar.HOUR_OF_DAY);

                smartgrid.PriceCriteria priceCrit = new PriceCriteria();
                priceCrit.add(Restrictions.eq("Tariff_Prices_role.id", tariff.getID()));

                priceCrit.add(Restrictions.sqlRestriction("((StartHour = " + dataLineHour + " and StartMinute <= " + dataLineMin + ")"
                        + " or StartHour < " + dataLineHour + ") and "
                        + "((EndHour = " + dataLineHour + " and EndMinute >= " + dataLineMin + ")"
                        + " or EndHour > " + dataLineHour + ")"));

                price = smartgrid.Price.loadPriceByCriteria(priceCrit);


                if (price != null) {
                    totalMoney += (dataline.getValue() * price.getEuros());
                    //System.out.println("Preco :"+price.getEuros() +"tariff "+price.getID());
                    dl = new DataLine();
                    dl.setValue(dataline.getValue() * price.getEuros());
                    dl.setContract(dataline.getContract());
                    dl.setHora(dataline.getHora());
                    dl.setMinuto(dataline.getMinuto());
                    dl.setTariff(tariff);
                    dl.setTimeStamp(dataline.getTimeStamp());
                    dl.setUnit(dataline.getUnit());
                    Dlines.add(dl);
                }

            }


            mstats.setTotal(totalMoney);

            //igual ao electric stats?????????
            mstats.setDataLine_role(Dlines);
            mstats.setEndTime(elStats.getEndTime());
            mstats.setStartTime(elStats.getStartTime());
            mstats.setInterval(elStats.getInterval());

            return mstats;
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStats.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    //copiado do moneyStats
    @Override
    public MoneyStats getMoneyStats(GregorianCalendar begin, GregorianCalendar end, Contract contract, short type) throws NoDataAvailableException {

        MoneyStats stats = new MoneyStats();
        GregorianCalendar startDate = new GregorianCalendar();
        GregorianCalendar endDate = new GregorianCalendar();

        startDate = (GregorianCalendar) begin.clone();
        endDate = (GregorianCalendar) end.clone();
        Date s = startDate.getTime();
        Date e = endDate.getTime();
        long dateDiff = e.getTime() - s.getTime();

        if (type == NONE || (type == WEEKS && (dateDiff / (1000 * 60 * 60 * 24)) < 7)
                || (type == DATELINE && (dateDiff / (1000 * 60 * 60 * 24)) >= SHOW_DAYS)) {
            if (dateDiff / (1000 * 60 * 60 * 24) <= SHOW_DAYS) {
                System.out.println("dataline");
                stats = (this.getMoneyStatsHour(begin, end, contract));
            } else if (dateDiff / (1000 * 60 * 60 * 24) > SHOW_DAYS && dateDiff / (1000 * 60 * 60 * 24) <= SHOW_MONTHS) {
                System.out.println("DIA");
                stats = this.getMoneyStatsByMaterializedDay(begin, end, contract);
            } else {
                System.out.println("MES");
                stats = this.getMoneyStatsByMaterializedMonth(begin, end, contract);
            }
        } else {
            switch (type) {
                case DATELINE:
                    stats = this.getMoneyStatsHour(begin, end, contract);
                    break;
                case DAYS:
                    stats = this.getMoneyStatsByMaterializedDay(begin, end, contract);
                    break;
                case WEEKS:
                    stats = this.getMoneyStatsByMaterializedWeeks(begin, end, contract);
                    break;
                case MONTHS:
                    stats = this.getMoneyStatsByMaterializedMonth(begin, end, contract);
                    if (stats == null) {
                        if (end.get(Calendar.MONTH) == new GregorianCalendar().get(Calendar.MONTH) && end.get(Calendar.YEAR) == new GregorianCalendar().get(Calendar.YEAR)) {
                            if ((begin.get(Calendar.MONTH) - 1) == new GregorianCalendar().get(Calendar.MONTH)) {
                                begin.set(Calendar.DAY_OF_MONTH, 1);
                                stats = this.getMoneyStatsByMaterializedMonth(begin, end, contract);
                            } else {
                                throw new NoDataAvailableException();
                            }
                        } else {
                            throw new NoDataAvailableException();
                        }
                    }
                    break;
                default:
                    stats = null;
                    break;
            }
        }
        return stats;
    }

    public MoneyStats getMoneyStatsByMaterializedDay(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        MoneyStats ele = new MoneyStats();
        double total = 0;
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(DAYS);

        TreeSet<DataLine> linha = new TreeSet<DataLine>();

        DiaAgregadoCriteria dCrit;
        try {
            dCrit = new DiaAgregadoCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));

            DiaAgregado[] dias = DiaAgregado.listDiaAgregadoByCriteria(dCrit);
            DiaAgregado d;
            DataLine dltemp;
            Iterator<DiaAgregado> i = (Arrays.asList(dias)).iterator();
            while (i.hasNext()) {
                d = i.next();
                dltemp = new DataLine();
                dltemp.setContract(d.getContract());
                dltemp.setTariff(d.getContract().getTariff_CurrentTarif_role());
                dltemp.setHora((short) 0);
                dltemp.setMinuto((short) 0);
                dltemp.setUnit("kW");
                dltemp.setTimeStamp(d.getData());
                dltemp.setValue(d.getPrecoSUM());
                total += d.getPrecoSUM();
                linha.add(dltemp);
            }

        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        ele.setDataLine_role(linha);
        ele.setTotal(total);
        return ele;
    }

    public MoneyStats getMoneyStatsByMaterializedMonth(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        MoneyStats ele = new MoneyStats();
        double total = 0;
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(MONTHS);
        TreeSet<DataLine> linha = new TreeSet<DataLine>();

        MesAgregadoCriteria dCrit;
        try {
            dCrit = new MesAgregadoCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));

            MesAgregado[] meses = MesAgregado.listMesAgregadoByCriteria(dCrit);
            MesAgregado d;
            DataLine dltemp;
            if (meses == null || Arrays.asList(meses).isEmpty()) {
                return null;
            }

            Iterator<MesAgregado> i = (Arrays.asList(meses)).iterator();
            while (i.hasNext()) {
                d = i.next();
                dltemp = new DataLine();
                dltemp.setContract(d.getContract());
                dltemp.setTariff(d.getContract().getTariff_CurrentTarif_role());
                dltemp.setHora((short) 0);
                dltemp.setMinuto((short) 0);
                dltemp.setUnit("kW");
                dltemp.setTimeStamp(d.getData());
                dltemp.setValue(d.getPrecoSUM());
                total += d.getPrecoSUM();
                linha.add(dltemp);
            }
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        ele.setDataLine_role(linha);
        ele.setTotal(total);
        return ele;
    }

    public MoneyStats getMoneyStatsByMaterializedWeeks(GregorianCalendar begin, GregorianCalendar end, Contract c) {
        MoneyStats ele = new MoneyStats();
        ele.setStartTime(begin);
        ele.setEndTime(end);
        ele.setInterval(WEEKS);
        TreeSet<DataLine> linha = new TreeSet<DataLine>();
        double total = 0;
        SemanaAgregadaCriteria dCrit;
        try {
            dCrit = new SemanaAgregadaCriteria();
            dCrit.add(Restrictions.between("data", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dCrit.add(Restrictions.eq("contract.id", c.getID()));
            dCrit.setProjection(Projections.projectionList().add(Projections.avg("domingoPrecoSUM")).add(Projections.avg("segundaPrecoSUM")).add(Projections.avg("tercaPrecoSUM")).add(Projections.avg("quartaPrecoSUM")).add(Projections.avg("quintaPrecoSUM")).add(Projections.avg("sextaPrecoSUM")).add(Projections.avg("sabadoPrecoSUM")).add(Projections.avg("domingoPrecoAVG")));
            Contract contrato = Contract.getContractByORMID(c.getID());
            DataLine dltemp;
            List<Object[]> rows = dCrit.list();

            if (rows != null && rows.get(0)[0] != null) {
                if (!(rows.isEmpty())) {
                    Object[] row = rows.get(0);
                    Tariff tarrif = null;
                    /* DOMINGO */
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[0]);
                    linha.add(dltemp);
                    /* SEGUNDA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = new DataLine();
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[1]);
                    linha.add(dltemp);
                    /* TERCA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[2]);
                    linha.add(dltemp);
                    /* QUARTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[3]);
                    linha.add(dltemp);
                    /* QUINTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[4]);
                    linha.add(dltemp);
                    /* SEXTA */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[5]);
                    linha.add(dltemp);
                    /* SABADO */
                    begin.add(Calendar.DAY_OF_YEAR, 1);
                    dltemp = criaDataline(contrato, tarrif, new java.sql.Date(begin.getTimeInMillis()), (Double) row[6]);
                    linha.add(dltemp);
                    for (int i = 0; i < 7; i++) {
                        total += (Double) row[i];
                    }
                }
            }

        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        ele.setDataLine_role(linha);
        ele.setTotal(total);
        return ele;
    }

    private MoneyStats getMoneyStatsHour(GregorianCalendar begin, GregorianCalendar end, Contract contract) {

        MoneyStats mStats = new MoneyStats();
        double total = 0;
        DataLineCriteria dtLineCrit;
        smartgrid.PriceCriteria priceCrit;
        Price price;
        DataLine horaMoney;
        try {
            dtLineCrit = new DataLineCriteria();
            dtLineCrit.add(Restrictions.between("timeStamp", ETL.gCalenderToSqlDate(begin), ETL.gCalenderToSqlDate(end)));
            dtLineCrit.add(Restrictions.eq("contract.id", contract.getORMID()));
            DataLine[] horas = DataLine.listDataLineByCriteria(dtLineCrit);
            TreeSet<DataLine> linha = new TreeSet<DataLine>();

            for (DataLine hora : horas) {
                priceCrit = new PriceCriteria();
                priceCrit.add(Restrictions.eq("Tariff_Prices_role.id", hora.getTariff().getID()));
                priceCrit.add(Restrictions.sqlRestriction("((StartHour = " + hora.getHora() + " and StartMinute <= " + hora.getMinuto() + ")"
                        + " or StartHour < " + hora.getHora() + ") and "
                        + "((EndHour = " + hora.getHora() + " and EndMinute >= " + hora.getMinuto() + ")"
                        + " or EndHour > " + hora.getHora() + ")"));
                price = smartgrid.Price.loadPriceByCriteria(priceCrit);
                if (price != null) {
                    total += hora.getValue() * price.getEuros();
                    horaMoney = new DataLine();
                    horaMoney.setValue(hora.getValue() * price.getEuros());
                    horaMoney.setContract(contract);
                    horaMoney.setHora(hora.getHora());
                    horaMoney.setMinuto(hora.getMinuto());
                    horaMoney.setTariff(hora.getTariff());
                    horaMoney.setTimeStamp(hora.getTimeStamp());
                    horaMoney.setUnit(hora.getUnit());


                    linha.add(horaMoney);//TODO fazer new hora
                }
            }
            mStats.setDataLine_role(linha);
            mStats.setInterval(DATELINE); //TODO
            mStats.setStartTime(begin);
            mStats.setEndTime(end);
            mStats.setTotal(total);
            return mStats;
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    //fim copia money stats

    public static void main(String[] args) {
        /*  CRIA NOVA SESSION  */
        // ArrayList<FaceBookAccount> amigos = new ArrayList<FaceBookAccount>();
        // amigos.add(FaceBookAccount.getFaceBookAccountByORMID(0));
        // amigos.add(FaceBookAccount.getFaceBookAccountByORMID(1));
        //  amigos.add(FaceBookAccount.getFaceBookAccountByORMID(2));
        EltricStatsManager em = new EltricStatsManager();
        try {
            MoneyStats m;
            //EltricStats ele = em.getElectricStatsByMaterializedWeeks(new GregorianCalendar(2010, 02, 21), new GregorianCalendar(2011, 07, 7), Contract.getContractByORMID(1));
            EltricStats ele = em.getEletricStats(new GregorianCalendar(2011, 2, 1), new GregorianCalendar(2011, 2, 5), Contract.getContractByORMID(1), DATELINE);
            Collection<DataLine> dataLinesGasto = ele.getDataLine_role();

            for (DataLine d : dataLinesGasto) {
                System.out.println("Valor gasto" + d.getValue());
            }

            Tariff tariff = new Tariff();
            TariffCriteria crit = new TariffCriteria();
            //crit.add(Restrictions.eq("ID", 8));
            tariff = Tariff.loadTariffByORMID(1);

            if (tariff != null) {
                m = em.getMoneyPrevisions(tariff, ele);
                Collection<DataLine> datalines = m.getDataLine_role();
                System.out.println("TOtal " + datalines.size());
                for (DataLine a : datalines) {
                    System.out.println("Valor " + a.getValue());
                }
            }

            //                List<Object[]> fb = d.list();
            //                
            //                for(Object[] c : fb){
            //                    System.out.println(c[0]);
            //                }



            //        EltricStatsManager.getDayLines(new GregorianCalendar());

            //        EltricStatsManager em = new EltricStatsManager();
            //        EltricStats ele = em.getElectricStatsByMaterializedWeeks(new GregorianCalendar(2010, 02, 21), new GregorianCalendar(2011, 07, 7), Contract.getContractByORMID(1));
            //        
            //        for (DataLine dl : ele.getDataLine_role()) {
            //            System.out.println(dl.getTimeStamp().toString() + " " + dl.getID() + " " + dl.getValue() + " " + dl.getContract().getID());
            //        }
        } catch (PersistentException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NoDataAvailableException ex) {
            Logger.getLogger(EltricStatsManager.class.getName()).log(Level.SEVERE, null, ex);
        }


    }
}
// for(Iterator it = insurances.iterator();it.hasNext();){
//Insurance insurance = 
//(Insurance) it.next();
//System.out.println("\nnumero de elementos "+datalines.size());
               /*     
smartgrid.DataLineCriteria dtCrit = new DataLineCriteria();
DataLineDetachedCriteria dlcrit = new DataLineDetachedCriteria();
Session s = Hibernate.
org.hibernate.Session session = Se

List dataline = (List) session.createCriteria(DataLine.class);
crit = new DiaAgregadoCriteria();
crit.add(Restrictions.between("data", start, end));
crit.setProjection(Projections.projectionList().add(Projections.avg("gastoAVG")).add(Projections.sum("gastoSUM")).add(Projections.avg("precoAVG")).add(Projections.sum("precoSUM")).add(Projections.groupProperty("contract.id")));
DiaAgregado[] dias = DiaAgregado.listDiaAgregadoByCriteria(crit);
Iterator<DiaAgregado> i = (Arrays.asList(dias)).iterator();
DiaAgregado [] results; 
dtCrit.add(Restrictions.eq("timestamp",day.YEAR+'-'+day.MONTH+'-'+day.DATE));
dtCrit.setProjection(Projections.projectionList().add(Projections.avg("value")).add(Projections.sum("value")).add(Projections.avg("precoAVG")).add(Projections.sum("precoSUM")).add(Projections.groupProperty("contract.id")));

//DiaAgregado[] dias = DiaAgregado.listDiaAgregadoByCriteria(dtCrit);
//Iterator<DiaAgregado> i = (Arrays.asList(dias)).iterator();*/
// dayLine d = new dayLine(Integer.MIN_VALUE, null, day, GastoAVG, GastoSUM, precoAVG, precoSUM)
       /* } catch (PersistentException ex) {
Logger.getLogger(EltricStats.class.getName()).log(Level.SEVERE, null, ex);
}*/
//return null;
//}
// Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
